package com.dm.saleassback.loginmanage.service.impl;

import com.dm.saleassback.common.util.EncryptionUtil;
import com.dm.saleassback.common.util.RemoveSave;
import com.dm.saleassback.loginmanage.mapper.LoginMapper;
import com.dm.saleassback.loginmanage.service.LoginService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class LoginServiceImpl implements LoginService{
    @Autowired
    private LoginMapper loginMapper;


    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        return DriverManager.getConnection("jdbc:sqlserver://192.168.1.135\\SQL2008;DatabaseName=DMHR", "binghe_2017_oa", "Binghe*5211314");
    }

    @Override
    public Map<String, Object> queryUserInfosByUsername(Map<String, Object> param, HttpServletRequest request) throws SQLException {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;
        String username1 = param.get("username")!=null?param.get("username").toString():null;
//        String sql = "select yg_ming as Name,yg_mm as Password,(select ming from jigoudiguib where id=yg_zhongdian) as Store,(select zhu from jigoudiguib where id=yg_zhongdian) as zhu,(select jibie from zwb where id=yg_zhiwu) as jb,(select ming from jigoudiguib where id=yg_bumen) as bm,(select gw from gwb where id=yg_gangwei) as gw,(select ming from zwb where id=yg_zhiwu) as zw from ygb where  yg_id="+username1+" and yg_zaizhi='在职' and yg_zaigang='在岗'";
        String sql = "select a.yg_ming as Name,a.yg_mm as Password,b.ming as bm,c.gw as gw,b.shibie as Store   from ygb a \n" +
                "LEFT JOIN jigoudiguib b on a.yg_bumen = b.id \n" +
                "LEFT JOIN gwb c on a.yg_gangwei=c.id \n" +
                "where a.yg_zaizhi='在职' and a.yg_zaigang='在岗'  and a.yg_id = "+username1;
        Map<String,Object> userMap = new HashMap<String, Object>();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            if(res.next()){
                String username = res.getString("Name");
                String Password = res.getString("Password");
                String Store = res.getString("Store");
                String bm = res.getString("bm");

                userMap.put("Name",username);
                userMap.put("Password",Password);
                userMap.put("Store",Store);
                userMap.put("bm",bm);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
            JdbcUtils.closeStatement(stat);
            JdbcUtils.closeResultSet(res);
        }
        Map<String,Object> statusMap = new HashMap<String,Object>();
        String inputPwd = param.get("password")!=null?param.get("password").toString():null;
        inputPwd = EncryptionUtil.getSha1(inputPwd);
        inputPwd = inputPwd.toUpperCase();
        String queryPwd = null;
        String userName = null;
        String bm = null;
        if(userMap!=null){
            queryPwd = userMap.get("Password")!=null?userMap.get("Password").toString():null;
            userName = userMap.get("Name")!=null?userMap.get("Name").toString():null;
            bm = userMap.get("bm")!=null?userMap.get("bm").toString():null;
        }
        if(userMap==null){
            //0代表没有当前用户
            statusMap.put("status","0");
        }else{
            if(inputPwd.equals(queryPwd)){

                //1代表有当前用户并且密码输入正确
                String store = userMap.get("Store").toString();
                String userno = param.get("username")!=null?param.get("username").toString():null;
                HttpSession session = request.getSession();
                session.setAttribute("userno",userno);
                session.setAttribute("username",userName);
                session.setAttribute("store",store);
                session.setAttribute("bm",bm);
                statusMap.put("status",1);
                statusMap.put("store",store);
                statusMap.put("bm",bm);
                statusMap.put("userno",userno);
                statusMap.put("username",userName);
            }else{
                //2代表有当前用户但是密码输入错误
                statusMap.put("status",2);
            }
        }

        return statusMap;
    }

    @Override
    public List<Map<String, Object>> queryZDStore(Map<String, Object> param) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;
        String sql = "select ming from jigoudiguib where zhu=0 and zt=0 and shibie!='外勤' order by jibie";
        List<Map<String,Object>> resultList = new ArrayList<Map<String, Object>>();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            while (res.next()){
                Map<String,Object> tempMap = new HashMap<String, Object>();
                String tempMing = res.getString("ming");
                tempMap.put("ming",tempMing);
                resultList.add(tempMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
            JdbcUtils.closeStatement(stat);
            JdbcUtils.closeResultSet(res);
        }


        return resultList;
    }

    /**
         * @Description 查询所有类别下拉框
         * @Author zhangxue
         * @Date 9:45 2018/6/3
         * @Param
         * @retrun
         **/
    @Override
    public List<Map<String, Object>> queryAllCategorySelect(Map<String, Object> param) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        resultList = loginMapper.queryAllCategorySelect(param);
        return resultList;
    }

    /**
         * @Description 查询所有项目下拉框
         * @Author zhangxue
         * @Date 9:48 2018/6/3
         * @Param
         * @retrun
         **/
    @Override
    public List<Map<String, Object>> queryAllProjectSelect(Map<String, Object> param) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        resultList = loginMapper.queryAllProjectSelect(param);
        return resultList;
    }

    /**
     * @Description 查询所有岗位标准下拉框
     * @Author zhangxue
     * @Date 9:48 2018/6/3
     * @Param
     * @retrun
     **/
    @Override
    public List<Map<String, Object>> queryAllStandardSelect(Map<String, Object> param) {
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        resultList = loginMapper.queryAllStandardSelect(param);
        return resultList;
    }

    /**
         * @Description 查询所有销售部员工信息
         * @Author zhangxue
         * @Date 15:28 2018/6/3
         * @Param
         * @retrun
         **/
    @Override
    public List<Map<String, Object>> queryAllSaleMan(Map<String, Object> param) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;
        String sql = "select a.id,a.yg_id,a.yg_ming,b.ming,b.shibie,c.gw,a.yg_mm from ygb a \n" +
                "LEFT JOIN jigoudiguib b on a.yg_bumen = b.id \n" +
                "LEFT JOIN gwb c on a.yg_gangwei=c.id \n" +
                "where a.yg_zaizhi='在职' and a.yg_zaigang='在岗' and c.gw LIKE '%销售%'";
        List<Map<String,Object>> resultList = new ArrayList<Map<String, Object>>();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            while (res.next()){
                Map<String,Object> tempMap = new HashMap<String, Object>();
                String id = res.getString("id");
                String yg_id = res.getString("yg_id");
                String yg_mm = res.getString("yg_mm");
                String yg_ming = res.getString("yg_ming");
                String ming = res.getString("ming");
                String shibie = res.getString("shibie");
                String gw = res.getString("gw");

                tempMap.put("id",id);
                tempMap.put("UserNo",yg_id);
//                tempMap.put("Password",yg_mm);
                tempMap.put("Name",yg_ming);
                tempMap.put("Bumen",ming);
                tempMap.put("Store",shibie);
                tempMap.put("Post",gw);
                resultList.add(tempMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
            JdbcUtils.closeStatement(stat);
            JdbcUtils.closeResultSet(res);
        }


        return resultList;
    }

    /**
     * @Description 查询所有销售部员工信息下拉
     * @Author zhangxue
     * @Date 15:28 2018/6/3
     * @Param
     * @retrun
     **/
    @Override
    public List<Map<String, Object>> queryAllSaleManSelect(Map<String, Object> param) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;
        String Store = param.get("Store")!=null?param.get("Store").toString():null;
        String Post = param.get("Post")!=null?param.get("Post").toString():null;
        if(Store==null||Post==null||"".equals(Store)||"".equals(Post)){
            return null;
        }
        String sql = "select a.id,a.yg_id,a.yg_ming,b.ming,b.shibie,c.gw,a.yg_mm from ygb a \n" +
                "LEFT JOIN jigoudiguib b on a.yg_bumen = b.id \n" +
                "LEFT JOIN gwb c on a.yg_gangwei=c.id \n" +
                "where a.yg_zaizhi='在职' and a.yg_zaigang='在岗' and c.gw LIKE '%销售%' "+
                "and b.shibie = '"+Store+"' and c.gw = '"+Post+"'";
        List<Map<String,Object>> resultList = new ArrayList<Map<String, Object>>();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            while (res.next()){
                Map<String,Object> tempMap = new HashMap<String, Object>();
                String id = res.getString("id");
                String yg_id = res.getString("yg_id");
                String yg_mm = res.getString("yg_mm");
                String yg_ming = res.getString("yg_ming");
                String ming = res.getString("ming");
                String shibie = res.getString("shibie");
                String gw = res.getString("gw");

                tempMap.put("id",id);
                tempMap.put("UserNo",yg_id);
//                tempMap.put("Password",yg_mm);
                tempMap.put("Name",yg_ming);
                tempMap.put("Bumen",ming);
                tempMap.put("Store",shibie);
                tempMap.put("Post",gw);
                resultList.add(tempMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
            JdbcUtils.closeStatement(stat);
            JdbcUtils.closeResultSet(res);
        }


        return resultList;
    }

    /**
         * @Description 查询所有销售部岗位
         * @Author zhangxue
         * @Date 15:37 2018/6/3
         * @Param
         * @retrun
         **/
    @Override
    public List<Map<String, Object>> queryAllSalePost(Map<String, Object> param) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;
        String sql = "select * from gwb where gw like '%销售%'";
        List<Map<String,Object>> queryList = new ArrayList<Map<String, Object>>();
        List<Map<String,Object>> resultList = new ArrayList<Map<String, Object>>();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            while (res.next()){
                Map<String,Object> tempMap = new HashMap<String, Object>();
                String gw = res.getString("gw");

                tempMap.put("Post",gw);
                queryList.add(tempMap);
            }
            resultList = RemoveSave.distinctBySetOrder(queryList);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
            JdbcUtils.closeStatement(stat);
            JdbcUtils.closeResultSet(res);
        }
        return resultList;
    }

    /**
     * @Description 查询所有销售部岗位下拉
     * @Author zhangxue
     * @Date 15:37 2018/6/3
     * @Param
     * @retrun
     **/
    @Override
    public List<Map<String, Object>> queryAllSalePostSelect(Map<String, Object> param) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;
        String Store = param.get("Store")!=null?param.get("Store").toString():null;
        if(Store==null||"".equals(Store)){
            return null;
        }
        String sql = "select c.gw from ygb a \n" +
                "LEFT JOIN jigoudiguib b on a.yg_bumen = b.id \n" +
                "LEFT JOIN gwb c on a.yg_gangwei=c.id \n" +
                "where a.yg_zaizhi='在职' and a.yg_zaigang='在岗' and c.gw LIKE '%销售%' and "+
                "b.shibie = '" + Store+"'";
        List<Map<String,Object>> queryList = new ArrayList<Map<String, Object>>();
        List<Map<String,Object>> resultList = new ArrayList<Map<String, Object>>();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            while (res.next()){
                Map<String,Object> tempMap = new HashMap<String, Object>();
                String gw = res.getString("gw");

                tempMap.put("Post",gw);
                queryList.add(tempMap);
            }
            resultList = RemoveSave.distinctBySetOrder(queryList);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
            JdbcUtils.closeStatement(stat);
            JdbcUtils.closeResultSet(res);
        }
        return resultList;
    }

    /**
     * @Description 查询所有销售部店面
     * @Author zhangxue
     * @Date 15:37 2018/6/3
     * @Param
     * @retrun
     **/
    @Override
    public List<Map<String, Object>> queryAllSaleStore(Map<String, Object> param) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;
        String sql = "select b.shibie from ygb a \n" +
                "LEFT JOIN jigoudiguib b on a.yg_bumen = b.id \n" +
                "LEFT JOIN gwb c on a.yg_gangwei=c.id \n" +
                "where a.yg_zaizhi='在职' and a.yg_zaigang='在岗'";
        List<Map<String,Object>> queryList = new ArrayList<Map<String, Object>>();
        List<Map<String,Object>> resultList = new ArrayList<Map<String, Object>>();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            while (res.next()){
                Map<String,Object> tempMap = new HashMap<String, Object>();
                String shibie = res.getString("shibie");

                tempMap.put("Post",shibie);
                queryList.add(tempMap);
            }
            resultList = RemoveSave.distinctBySetOrder(queryList);
            for(int i=0;i<resultList.size();i++){
                if(resultList.get(i).get("Post")==null||"null".equals(resultList.get(i).get("Post").toString())){
                    resultList.remove(i);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
            JdbcUtils.closeStatement(stat);
            JdbcUtils.closeResultSet(res);
        }
        return resultList;
    }
}
